package com.xgp.Excel.poi;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.Test;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Date;
import java.util.Random;

/**
 * 写测试
 */
public class ExcelRead {

    String PATH = "D:\\develop\\java\\IDEA2019\\code\\qq\\src\\test\\java\\com\\xgp\\Excel\\poi\\";

    @Test
    public void test1() throws IOException {

        //获取文件流
        FileInputStream in = new FileInputStream(PATH + "03版本的Excel.xls");

        //1、创建一个工作溥
        Workbook workbook = new HSSFWorkbook(in);//03版的
        //2、创建一个工作表
        Sheet sheet = workbook.getSheetAt(0);
        Row row = sheet.getRow(0);
        Cell cell = row.getCell(0);

        System.out.println(cell.getStringCellValue());
    }

    /**
     * 判断不同的类型
     * @throws IOException
     */
    @Test
    public void test2() throws IOException {

        //获取文件流
        FileInputStream in = new FileInputStream(PATH + "03版本的Excel.xls");

        //1、创建一个工作溥
        Workbook workbook = new HSSFWorkbook(in);//03版的
        //2、创建一个工作表
        Sheet sheet = workbook.getSheetAt(0);
        Row row = sheet.getRow(0);
        if(row != null) {
            int cellCount = row.getPhysicalNumberOfCells();
            for(int cellNum = 0;cellNum < cellCount;cellNum++) {
                Cell cell = row.getCell(cellNum);
                if(cell != null) {
                    int cellType = cell.getCellType();
                    String cellValue = cell.getStringCellValue();
                    System.out.print(cell + " | ");
                }
            }
            System.out.println();
        }

        int rowCount = sheet.getPhysicalNumberOfRows();
        for(int rowNum = 1;rowNum < rowCount;rowNum++) {
            Row rowData = sheet.getRow(rowNum);
            if(rowData != null) {
                int cellCount = row.getPhysicalNumberOfCells();
                for(int cellNum = 0;cellNum < cellCount;cellNum++){
                    System.out.print("[" + (rowNum + 1) + '-' + (cellNum + 1) + "]");
                    Cell cell = rowData.getCell(cellNum);
                    //匹配列的数目
                    if(cell != null) {
                        int cellType = cell.getCellType();
                        String cellValue = "";
                        switch (cellType) {
                            case HSSFCell
                                    .CELL_TYPE_STRING:
                                System.out.print("【String】");
                                cellValue = cell.getStringCellValue();
                                break;
                            case HSSFCell.CELL_TYPE_NUMERIC://数字
                                System.out.print("【number】");
                                if(HSSFDateUtil.isCellDateFormatted(cell)) {
                                    System.out.print("【日期】");
                                    Date date = cell.getDateCellValue();
                                    cellValue = new DateTime(date).toString("yyyy-MM-dd");
                                }else {
                                    System.out.print("【字符串】");
                                    cell.setCellValue(HSSFCell.CELL_TYPE_STRING);
                                    cellValue = cell.toString();
                                }
                                break;
                            case HSSFCell.CELL_TYPE_ERROR:
                                System.out.print("数据类型错误");
                                break;
                            case HSSFCell.CELL_TYPE_BLANK:
                                if(true) {
                                    if(true) {
                                        if(true) {
                                            if(true) {
                                                if(true) {
                                                    if(true) {
                                                        if(true) {
                                                            if(true) {
                                                                if(true) {
                                                                    if(true) {

                                                                    }
                                                                }
                                                            }
                                                        }
                                                    }
                                                }
                                            }
                                        }
                                    }
                                }
                                break;
                        }
                    }
                }
            }
        }
        in.close();
    }

    @Test
    public void test3() throws IOException {
        FileInputStream in = new FileInputStream(PATH + "公式.xls");
        Workbook workbook = new HSSFWorkbook(in);
        Sheet sheet = workbook.getSheetAt(0);

        Row row = sheet.getRow(4);
        Cell cell = row.getCell(0);

        //拿到计算公式
        FormulaEvaluator hssfFormulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);

        //输出单元格的内容
        int cellType = cell.getCellType();
        switch (cellType) {
            case Cell.CELL_TYPE_FORMULA://公式
                String cellFormula = cell.getCellFormula();
                System.out.println(cellFormula);
                break;

/*                //计算
                CellValue evaluate = FormulaEvaluator.evaluate(cell);
                break;
            default:
                throw new IllegalStateException("Unexpected value: " + cellType);*/
        }
    }
}
